import pymysql
import pandas as pd
from Database import DatabaseHandler

db_handler = DatabaseHandler(host='localhost', port=3306, user='root', password='123456')
db_handler.create_database('test')
db_handler.cursor.execute('USE test')
# 创建表
sql = """  
create table if not exists profession_introduction(  
    profession_name varchar(225) primary key,  
    profession_introduction mediumtext,  
    Professional_details mediumtext  
)  
"""
db_handler.cursor.execute(sql)
print('表创建或已存在，SQL执行成功')

# 读取Excel文件
df = pd.read_excel(r"C:\Users\姚宇康\Desktop\日常文件\学习\专业简介.xlsx")
df.fillna('', inplace=True)  # 填充空值

sql_insert = 'INSERT INTO profession_introduction VALUES (%s, %s, %s)'
try:
    db_handler.cursor.executemany(sql_insert, df.to_records(index=False).tolist())
    print(db_handler.cursor.rowcount, "记录插入成功。")
except pymysql.MySQLError as e:
    print(f"数据库错误：{e}")
    # 注意：由于autocommit=True，通常不需要手动回滚

db_handler.close_connection()